import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Properties;
import java.util.ArrayList;

import java.io.IOException;
import java.io.FileInputStream;


public class DataBase
{
/**Database to store various teams and players
* @author JMR 27/5/2010
*/

  protected Connection database;
  protected int next_player_id = 0;
  protected int next_team_id = 0;

  public DataBase ()
  {
    try{
      get_connection();
    }
    catch (IOException e){
      System.err.println("Error was expecting a database.properties file");
      e.printStackTrace();
    }
    catch (SQLException e){
      e.printStackTrace();
    }
  }

  private void get_connection() throws IOException, SQLException
  {
    // Load properties

    FileInputStream in = new FileInputStream("database.properties");
    Properties props = new Properties();
    props.load(in);

    // Define JDBC driver
    // Setting standard system property jdbc.drivers
    // is an alternative to loading the driver manually
    // by calling Class.forName()
    String drivers = props.getProperty("jdbc.drivers");
    System.setProperty("jdbc.drivers", drivers);

    // Obtain access parameters and use them to create connection
    String url = props.getProperty("jdbc.url");
    String user = props.getProperty("jdbc.user");
    String password = props.getProperty("jdbc.password");

    database = DriverManager.getConnection(url, user, password);
  }

  private Player get_player (int id)
  {
    Player player = null;
    try{
      Statement statement = database.createStatement();
      ResultSet results = statement.executeQuery(
       "SELECT * FROM players WHERE id = '" + id + "'");
      results.next();
      String name = results.getString("name");
      int attack = results.getInt("attack");
      int defence = results.getInt("defence");
      int goalkeeping = results.getInt("goalkeeping");
      int passing = results.getInt("passing");
      player = new Player (id,name,attack,defence,goalkeeping,passing);
    }
    catch (Exception e){
      e.printStackTrace();
      System.exit(1);
    } 

    return player;
  }

  ////////////////////
/////////////////WARNING NOT CORRECT - MANAGER BIT NOT BEEN IMPLEMENTED PROPERLY
  public Team load_team (int id)
  {
    Team team = null;
    try{
      Statement statement = database.createStatement();
      //get the squad
      ResultSet results = statement.executeQuery(
       "SELECT player FROM squads, WHERE team = '" + id + "'");
      ArrayList <Player> players = new ArrayList <Player> ();
      while (results.next()){
        int player_id = results.getInt("player");
        Player player = get_player(player_id);
        players.add(player);
      }

      Player [] squad = new Player [players.size()];
      for (int i = 0; i < players.size(); i++){
        squad[i] = players.get(i);
      }

      //get the actual team
      results = statement.executeQuery(
       "SELECT * FROM teams WHERE id = '" + id + "'");
      results.next();
      String name = results.getString("name");
      int manager = results.getInt("manager");
      team = new Team (id,name,squad, new Computer());
    }
    catch (Exception e){
      e.printStackTrace();
      System.exit(1);
    } 

    return team;
  }
}
